package com.bx.implatform.growUp.mapper;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Select;

import java.util.List;
import java.util.Map;

@Mapper
public interface CollectFeeMapper {

    @Select("""
            SELECT
                (SELECT name FROM data_center.pub_organ WHERE id = org_branch_tag) AS group_key,
                TO_CHAR(stat_date, 'YYYY-MM') AS x,
                SUM(traffic_flow) AS y
            FROM
                data_center.station_traffic_income_stats
            WHERE TO_CHAR(stat_date, 'YYYY-MM') BETWEEN TO_CHAR(CURRENT_DATE - CAST('12 months' AS INTERVAL), 'YYYY-MM') 
            AND TO_CHAR(CURRENT_DATE, 'YYYY-MM')
            GROUP BY
                org_branch_tag,
                TO_CHAR(stat_date, 'YYYY-MM')
            ORDER BY
                x DESC
            """)
    List<Map<String, Object>> getTrafficStats();

    @Select("""
            SELECT
                (SELECT name FROM data_center.pub_organ WHERE id = org_branch_tag) AS group_key,
                TO_CHAR(stat_date, 'YYYY-MM') AS x,
                SUM(income_amount) AS y
            FROM
                data_center.station_traffic_income_stats
            WHERE TO_CHAR(stat_date, 'YYYY-MM') BETWEEN TO_CHAR(CURRENT_DATE - CAST('12 months' AS INTERVAL), 'YYYY-MM') 
            AND TO_CHAR(CURRENT_DATE, 'YYYY-MM')
            GROUP BY
                org_branch_tag,
                TO_CHAR(stat_date, 'YYYY-MM')
            ORDER BY
                x DESC
            """)
    List<Map<String, Object>> getMoneyStats();


    @Select("""
            SELECT
            	vehicle_type,
            	sum(traffic) AS traffic,
            	to_char(stat_date_d, 'MM') AS MONTH
            FROM
            	(
            		SELECT
            			`vehicle_type`,
            			`traffic`,
            			to_date(stat_date, 'yyyy-MM') AS stat_date_d
            		FROM
            			`data_center`.`vehicle_type_income_day`
            		WHERE
            			stat_date >= trunc(now(), 'year')
            	)
            GROUP BY
            	MONTH,
            	vehicle_type
            ORDER BY
            	MONTH;
            """)
    List<Map<String, Object>> carFlow();
}
